-- -------------- --
-- openXDA Schema --
-- -------------- --

USE [openXDA]
GO

ALTER TABLE AccessLog ADD NodeID uniqueidentifier NOT NULL DEFAULT('00000000-0000-0000-0000-000000000000')
GO

ALTER TABLE Setting ADD UNIQUE (Name)
GO

DECLARE @DF_FileGroup_Error NVARCHAR(100)
SELECT @DF_FileGroup_Error = CONCAT
(
    'ALTER TABLE FileGroup DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('FileGroup') AND
            sys.columns.name = 'Error'
    )
)
EXECUTE sp_executesql @DF_FileGroup_Error
GO

ALTER TABLE FileGroup
	DROP COLUMN Error
GO

ALTER TABLE FileGroup
ADD ProcessingStatus INT NOT NULL DEFAULT(0)
GO

ALTER TABLE APIAccessKey ADD AllowImpersonation BIT NOT NULL DEFAULT 0
GO

INSERT INTO NodeType VALUES('Grafana', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Grafana.GrafanaHostingNode')
GO

ALTER TABLE Node ADD AssignedHostRegistrationID INT NULL REFERENCES HostRegistration(ID)
GO

CREATE TABLE GenerationAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

CREATE TABLE StationAuxAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

CREATE TABLE StationBatteryAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

-- Generation Model
CREATE VIEW Generation AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN GenerationAttributes ON Asset.ID = GenerationAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Generation ON GENERATION
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Generation') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO GenerationAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Generation ON GENERATION
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Generation Model Triggers 
-- Station Auxilary Model
CREATE VIEW StationAux AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationAuxAttributes ON Asset.ID = StationAuxAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Aux ON STATIONAUX
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationAux') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationAuxAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Aux ON STATIONAUX
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Auxilary Model Triggers 
-- Station Battery Model
CREATE VIEW StationBattery AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationBatteryAttributes ON Asset.ID = StationBatteryAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Battery ON STATIONBATTERY
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationBattery') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationBatteryAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Battery ON STATIONBATTERY
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Bettery Model Triggers

--PQMeasurement Updates
ALTER TABLE PQMeasurement ADD Unit VARCHAR(200) NOT NULL DEFAULT('None')
GO

UPDATE PQMeasurement SET Unit = (SELECT Name FROM Unit WHERE Unit.ID = PQMeasurement.UnitID)
GO

DECLARE @FK_PQMeasurement_UnitID NVARCHAR(100)
SELECT @FK_PQMeasurement_UnitID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'UnitID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_UnitID
GO

DECLARE @FK_PQMeasurement_PhaseID NVARCHAR(100)
SELECT @FK_PQMeasurement_PhaseID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'PhaseID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_PhaseID
GO

DECLARE @FK_PQMeasurement_HarmonicGroup NVARCHAR(100)
SELECT @FK_PQMeasurement_HarmonicGroup = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'HarmonicGroup' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_HarmonicGroup
GO

DECLARE @FK_PQMeasurement_Enabled NVARCHAR(100)
SELECT @FK_PQMeasurement_Enabled = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'Enabled' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_Enabled
GO

DECLARE @DF_PQMeasurement_HarmonicGroup NVARCHAR(100)
SELECT @DF_PQMeasurement_HarmonicGroup = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'HarmonicGroup'
    )
)
EXECUTE sp_executesql @DF_PQMeasurement_HarmonicGroup
GO

DECLARE @DF_PQMeasurement_Enabled NVARCHAR(100)
SELECT @DF_PQMeasurement_Enabled = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'Enabled'
    )
)
EXECUTE sp_executesql @DF_PQMeasurement_Enabled
GO

ALTER TABLE PQMeasurement DROP COLUMN
	UnitID,
	PhaseID,
	HarmonicGroup,
	Enabled
GO

ALTER TABLE PQMeasurement ADD Unit VARCHAR(200) NOT NULL DEFAULT('None')
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementTypeID
ON PQMeasurement(MeasurementTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementCharacteristicID
ON PQMeasurement(MeasurementCharacteristicID ASC)
GO

ALTER TABLE ChannelGroupType
ADD Unit VARCHAR(200) NOT NULL Default('None')
GO

UPDATE ChannelGroupType SET Unit = (SELECT Name FROM Unit WHERE Unit.ID = ChannelGroupType.UnitID)
GO

ALTER TABLE ChannelGroupType
DROP COLUMN UnitID
GO


CREATE TABLE ChannelTemplateFile
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    FileBlob VARBINARY(MAX) NOT NULL,
    FileName VARCHAR(MAX) NOT NULL,
    ShowTrend BIT NOT NULL DEFAULT(1),
    ShowEvents BIT NOT NULL DEFAULT(1),
    SortOrder INT NOT NULL DEFAULT(0)
)
GO

CREATE TABLE MATLABAnalytic
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssemblyName VARCHAR(MAX) NOT NULL,
    MethodName VARCHAR(MAX) NOT NULL,
    SettingSQL VARCHAR(MAX) NOT NULL DEFAULT '',
    LoadOrder INT NOT NULL DEFAULT 0
)
GO

CREATE TABLE MATLABAnalyticAssetType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MATLABAnalyticID INT NOT NULL REFERENCES MATLABAnalytic(ID),
    AssetTypeID INT NOT NULL REFERENCES AssetType(ID)
)
GO

CREATE TABLE MATLABAnalyticEventType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MATLABAnalyticID INT NOT NULL REFERENCES MATLABAnalytic(ID),
    EventTypeID INT NOT NULL REFERENCES EventType(ID)
)
GO


ALTER TABLE EventEventTag ADD TagData VARCHAR(MAX) NULL
GO


CREATE TABLE MeterDependentAssetDesignation
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterAssetID INT NOT NULL REFERENCES MeterAsset(ID),
    Designation VARCHAR(200) NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_MeterDependentAssetDesignation_MeterAssetID
ON MeterDependentAssetDesignation(MeterAssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultDetectionLogic_MeterAssetID
ON FaultDetectionLogic(MeterAssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmGroup_AlarmTypeID
ON AlarmGroup(AlarmTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmGroup_SeverityID
ON AlarmGroup(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_AlarmGroupID
ON Alarm(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_SeriesID
ON Alarm(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_AlarmGroupID
ON AlarmFactor(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_SeverityID
ON AlarmFactor(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmValue_AlarmID
ON AlarmValue(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmValue_AlarmDayID
ON AlarmValue(AlarmDayID ASC)
GO

ALTER TABLE AlarmLog ADD SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID) DEFAULT(4)
GO

CREATE TABLE LatestAlarmLog
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AlarmID INT NOT NULL UNIQUE REFERENCES Alarm(ID),
    AlarmLogID INT NOT NULL REFERENCES AlarmLog(ID),
    SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID),
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NULL
)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmID
ON LatestAlarmLog(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmLogID
ON LatestAlarmLog(AlarmLogID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_SeverityID
ON LatestAlarmLog(SeverityID ASC)
GO

CREATE TRIGGER AlarmLog_UpdateLatestAlarmLog
    ON AlarmLog
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Update existing records in LatestAlarmLog
    UPDATE LatestAlarmLog
    SET
        AlarmLogID = inserted.ID,
        SeverityID = inserted.SeverityID,
        StartTime = inserted.StartTime,
        EndTime = inserted.EndTime
    FROM
        LatestAlarmLog JOIN
        inserted ON inserted.AlarmID = LatestAlarmLog.AlarmID JOIN
        AlarmLog ON LatestAlarmLog.AlarmLogID = AlarmLog.ID
    WHERE inserted.StartTime >= AlarmLog.StartTime

    -- Determine which alarms have never had logs before now
    SELECT DISTINCT inserted.AlarmID
    INTO #alarm
    FROM
        inserted LEFT OUTER JOIN
        LatestAlarmLog ON inserted.AlarmID = LatestAlarmLog.AlarmID
    WHERE LatestAlarmLog.ID IS NULL

    -- Insert new records in LatestAlarmLog
    -- for alarms that never had logs before now
    INSERT INTO LatestAlarmLog(AlarmID, AlarmLogID, SeverityID, StartTime, EndTime)
    SELECT
        AlarmLog.AlarmID,
        AlarmLog.ID AlarmLogID,
        AlarmLog.SeverityID,
        AlarmLog.StartTime,
        AlarmLog.EndTime
    FROM
        #alarm CROSS APPLY
        (
            SELECT TOP 1 *
            FROM inserted
            WHERE AlarmID = #alarm.AlarmID
            ORDER BY StartTime DESC, ID DESC
        ) AlarmLog
END
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayID
ON AlarmDayGroupAlarmDay(AlarmDayID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayGroupID
ON AlarmDayGroupAlarmDay(AlarmDayGroupID ASC)
GO

DROP VIEW AlarmGroupView
GO

CREATE VIEW AlarmGroupView AS 
SELECT 
	AlarmGroup.ID,
	AlarmGroup.Name,
	AlarmSeverity.Name AlarmSeverity,
	CountStats.ChannelCount Channels,
	CountStats.MeterCount Meters,
	LastAlarm.StartTime LastAlarmStart,
	LastAlarm.EndTime LastAlarmEnd,
	LastAlarm.ChannelName LastChannel,
	LastAlarm.MeterName LastMeter,
	AlarmType.Description AS AlarmType
FROM 
	AlarmGroup LEFT JOIN
	AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN
	AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT Channel.ID) ChannelCount,
            COUNT(DISTINCT Channel.MeterID) MeterCount
        FROM
            Channel JOIN
            Series ON Series.ChannelID = Channel.ID JOIN
            Alarm ON Alarm.SeriesID = Series.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
    ) CountStats OUTER APPLY
    (
        SELECT TOP 1
            LatestAlarmLog.StartTime,
            LatestAlarmLog.EndTime,
            Channel.Name ChannelName,
            Meter.Name MeterName
        FROM
            Alarm JOIN
            Series ON Alarm.SeriesID = Series.ID JOIN
            Channel ON Series.ChannelID = Channel.ID JOIN
            Meter ON Channel.MeterID = Meter.ID JOIN
            LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
        ORDER BY
            LatestAlarmLog.StartTime DESC,
            LatestAlarmLog.AlarmLogID DESC
    ) LastAlarm
GO

DROP VIEW ActiveAlarmView
GO

CREATE VIEW ActiveAlarmView AS
SELECT
    Alarm.ID AS AlarmID,
    Alarm.AlarmGroupID AS AlarmGroupID,
    AlarmGroup.AlarmTypeID AS AlarmTypeID,
    AlarmFactor.ID AS AlarmFactorID,
    AlarmFactor.SeverityID,
    Alarm.SeriesID AS SeriesID,
    AlarmFactor.Factor AS Value
FROM
    (
        SELECT ID, Factor, AlarmGroupID, SeverityID
        FROM AlarmFactor
        UNION
        SELECT
            NULL AS ID,
            1.0 AS Factor,
            AlarmGroup.ID AS AlarmGroupID,
            AlarmGroup.SeverityID
        FROM AlarmGroup
    ) AlarmFactor LEFT JOIN
    Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN
    AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID
GO

ALTER TABLE NoteType ADD Label VARCHAR(200)
GO

UPDATE NoteType SET Label = 'Meter' WHERE Name = 'Meter'
GO

UPDATE NoteType SET Label = 'Event' WHERE Name = 'Event'
GO

UPDATE NoteType SET Label = 'Asset' WHERE Name = 'Asset'
GO

UPDATE NoteType SET Label = 'Substation' WHERE Name = 'Location'
GO

UPDATE NoteType SET Label = 'Customer' WHERE Name = 'Customer'
GO

UPDATE NoteType SET Label = 'User' WHERE Name = 'User'
GO

DELETE FROM NoteType WHERE Name = 'Company'
GO

DROP VIEW ActiveSubscription
GO

CREATE VIEW ActiveSubscription AS
	SELECT 
		UserAccountEmailType.ID AS UserAccountEmailID,
		UserAccountEmailType.UserAccountID AS UserAccountID,
		UserAccountEmailType.Approved AS Approved,
		AssetGroup.Name AS AssetGroup,
		EmailType.Name AS EmailName,
		EmailCategory.Name AS Category,
		EmailType.ID AS EmailTypeID,
		SentEmail.Subject AS Subject,
		SentEmail.TimeSent AS LastSent,
        UserAccount.Name AS UserName,
		UserAccount.Email AS Email,
        EmailType.RequireApproval AS RequireApproval
	FROM UserAccountEmailType LEFT JOIN
		AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID LEFT JOIN
		EmailType ON UserAccountEmailType.EmailTypeID = EmailType.ID LEFT JOIN
		EmailCategory ON EmailCategory.ID = EmailType.EmailCategoryID LEFT JOIN
		SentEmail ON SentEmail.EmailTypeID = EmailType.ID  LEFT JOIN
		UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID
GO

DROP VIEW AssetAssetGroupView
GO

CREATE VIEW AssetAssetGroupView
 AS
 SELECT
    AssetAssetGroup.ID,
    Asset.AssetKey AS AssetName,
    Asset.AssetName AS LongAssetName,
    Asset.ID AS AssetID,
	AssetType.Name AS AssetType,
	(SELECT Top 1 LocationKey FROM Location WHERE Location.ID IN (SELECT LocationID FROM AssetLocation WHERE AssetLocation.AssetID = Asset.ID)) AS AssetLocation,
    AssetGroupID,
    AssetGroup.Name,
    AssetGroup.DisplayDashboard
 FROM
    AssetAssetGroup JOIN
    Asset ON AssetAssetGroup.AssetID = Asset.ID LEFT JOIN
    AssetGroup ON AssetAssetGroup.AssetGroupID = AssetGroup.ID JOIN
    AssetType ON Asset.AssetTypeID = AssetType.ID
GO

DROP FUNCTION RecursiveMeterSearch
GO

CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int)
RETURNS TABLE
AS
RETURN
    WITH AssetGroupHierarchy AS
    (
        SELECT
            AssetGroupAssetGroup.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            1 Depth
        FROM AssetGroupAssetGroup
        WHERE AssetGroupAssetGroup.ParentAssetGroupID = @assetGroupID
        UNION ALL
        SELECT
            AssetGroupHierarchy.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            AssetGroupHierarchy.Depth + 1 Depth
        FROM
            AssetGroupHierarchy JOIN
            AssetGroupAssetGroup ON AssetGroupHierarchy.ChildAssetGroupID = AssetGroupAssetGroup.ParentAssetGroupID
        WHERE AssetGroupHierarchy.Depth < 10
    )
    SELECT DISTINCT MeterID AS ID
    FROM
        MeterAssetGroup LEFT JOIN
        AssetGroupHierarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHierarchy.ChildAssetGroupID
    WHERE
        MeterAssetGroup.AssetGroupID = @assetGroupID OR
        MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHierarchy)
GO

--XDA mainpage layout update
--TRUNCATE TABLE PQApplications
--GO
--DELETE FROM PQApplicationsCategory
--GO

--INSERT INTO PQApplicationsCategory (Name,SortOrder) VALUES ('Configure',1)
--GO
--INSERT INTO PQApplicationsCategory (Name, SortOrder) VALUES ('Visualize',3)
--GO
--INSERT INTO PQApplicationsCategory (Name, SortOrder) VALUES ('Interval Data',4)
--GO
--INSERT INTO PQApplicationsCategory (Name,SortOrder) VALUES ('Collection',2)
--GO

--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('System Center', 'http://localhost:8987','./Images/Tiles/SystemCenter.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Configure'),0)
--GO
--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('PQ Dashboard', 'http://localhost/PQDashboard','./Images/Tiles/PQDashboard.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Visualize'),1)
--GO
--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('PQ Browser', 'http://localhost/PQBrowser','./Images/Tiles/PQBrowser.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Visualize'),2)
--GO
--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('SPCTools', 'SPCTools/index.cshtml','./Images/Tiles/SPCTools.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Interval Data'),0)
--GO
--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('openMIC', 'http://localhost:8089','./Images/Tiles/openMIC.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Collection'),0)
--GO
--INSERT INTO PQApplications (Name,URL,Image,CategoryID,SortOrder) VALUES ('Notification Pages', 'http://localhost/NotificationPages','./Images/Tiles/NotificationPages.png',(SELECT ID FROM PQApplicationsCategory WHERE NAME='Configure'),1)
--GO

ALTER TABLE StandardMagDurCurve DROP
	XHigh,
	XLow,
	YHigh,
	YLow,
	UpperCurve,
	LowerCurve
GO

ALTER TABLE StandardMagDurCurve ADD Color VARCHAR(255) NOT NULL DEFAULT('#007a29')
GO

-- --------------- --
-- DefaultSettings --
-- --------------- --

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('COMTRADE.Root2AdjustmentQuery', 'SELECT ID FROM Meter WHERE Make = ''SEL''', 'SELECT ID FROM Meter WHERE Make = ''SEL''')
GO

--Grafana--
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.AdminUser', 'admin', 'admin')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.AuthProxyHeaderName', 'X-WEBAUTH-USER', 'X-WEBAUTH-USER')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.BasePath', 'Grafana', 'Grafana')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.HostedURL', 'http://localhost:8185', 'http://localhost:8185')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.InitializationTimeout', '30', '30')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.LastDashboardCookieName', 'x-last-dashboard', 'x-last-dashboard')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.OrganizationID', '1', '1')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Grafana.ServerPath', 'Grafana\bin\grafana-server.exe', 'Grafana\bin\grafana-server.exe')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.RestorationURL', 'http://localhost:8989/RestoreEventEmail.cshtml', 'http://localhost:8989/RestoreEventEmail.cshtml')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FileProcessor.FileGroupingPattern', '^(?<FileGroupKey>.*)\.[^\.]*$', '^(?<FileGroupKey>.*)\.[^\.]*$')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('HIDS.HistogramBucket', 'histogram_bucket', 'histogram_bucket')
GO

INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('FTT','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.FaultTraceTool.FTTDataSource','ftt')
GO

INSERT INTO ScheduledEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('AZURE','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.AzureDataSource','azure')
GO

-- Standard MagDur Curves --
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'NERC PRC-024-2', NULL, '#d3d3d3')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Transients', NULL, '#afd8f8')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Sag', NULL, '#f47d23')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Swell', NULL, '#008c48')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Mom. Interruption', NULL, '#ee2e2f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Sag', NULL, '#737373')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Swell', NULL, '#662c91')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temp. Interruption', NULL, '#bd9b33')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Sag', NULL,'#ff904f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Swell', NULL, '#ff9999')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Sustained Int.', NULL, '#0029A3')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Undervoltage', NULL, '#cb4b4b')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Overvoltage', NULL, '#4da74d')
GO
UPDATE StandardMagDurCurve SET Color = '#007a29' WHERE Name LIKE 'ITIC'
GO
UPDATE StandardMagDurCurve SET Color = '#edc240' WHERE Name LIKE 'SEMI F47'
GO
UPDATE StandardMagDurCurve SET Color = '#a30000' WHERE Name LIKE 'IEEE 1668 Type I & II'
GO
UPDATE StandardMagDurCurve SET Color = '#185aa9' WHERE Name LIKE 'IEEE 1668 Type III'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.000001 0, 0.01 0, 0.01 5.0, 0.000001 5.0, 0.000001 0))' WHERE Name = 'IEEE 1159 Transients'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.1, 0.5 0.1, 0.5 0.9, 0.01 0.9, 0.01 0.1))' WHERE Name = 'IEEE 1159 Instantaneous Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 1.1, 0.5 1.1, 0.5 1.8, 0.01 1.8, 0.01 1.1))' WHERE Name = 'IEEE 1159 Instantaneous Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0, 3.0 0, 3.0 0.1, 0.01 0.1, 0.01 0))' WHERE Name = 'IEEE 1159 Mom. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 0.1, 3.0 0.1, 3 0.9, 0.5 0.9, 0.5 0.1))' WHERE Name = 'IEEE 1159 Momentary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 1.1, 3.0 1.1, 3.0 1.4, 0.5 1.4, 0.5 1.1))' WHERE Name = 'IEEE 1159 Momentary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0, 60.0 0, 60.0 0.1, 3.0 0.1, 3.0 0))' WHERE Name = 'IEEE 1159 Temp. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0.1, 60.0 0.1, 60.0 0.9, 3.0 0.9, 3.0 0.1))' WHERE Name = 'IEEE 1159 Temporary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 1.1, 60.0 1.1, 60.0 1.2, 3.0 1.2, 3.0 1.1))' WHERE Name = 'IEEE 1159 Temporary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0, 1000.0 0, 1000.0 0.1, 60.0 0.1, 60.0 0))' WHERE Name = 'IEEE 1159 Sustained Int.'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0.8, 1000.0 0.8, 1000.0 0.9, 60.0 0.9, 60.0 0.8))' WHERE Name = 'IEEE 1159 Undervoltage'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 1.1, 1000.0 1.1, 1000.0 1.2, 60.0 1.2, 60.0 1.1))' WHERE Name = 'IEEE 1159 Overvoltage'
GO

--Delete unneeded Settings
DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Enabled'
GO
DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Frequency'
GO
DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Verbose'
GO
DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Enabled'
GO
DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Frequency'
GO
DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Verbose'
GO



-- ------------------- --
-- DefaultChannelTypes --
-- ------------------- --

UPDATE ValueList SET AltValue = Value WHERE GroupID = (SELECT ID FROM ValueListGroup WHERE Name ='Unit')
GO

--Update PQMeasurement entries
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('RMS Voltage', 'RMS Voltage', 'Volts', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S2/S1)', 'Voltage Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S0/S1)', 'Voltage Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Flicker Pst', 'Short Term Flicker Perceptibility (Pst)', 'Per Unit', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage THD', 'Voltage THD', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Harmonic', 'Voltage Harmonic', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S2/S1)', 'Current Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S0/S1)', 'Current Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current THD', 'Current THD', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Harmonic', 'Current Harmonic', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Active Power', 'Active Power', 'KW', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Reactive Power', 'Reactive Power', 'KVAR', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Apparent Power', 'Apparent Power', 'KVA', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Power Factor', 'Power Factor', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF'))
GO

--Add new Asset types
SET IDENTITY_INSERT AssetType ON
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (9,'StationAux','Station Auxiliary')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (10,'StationBattery','Station Battery')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (11,'Generation','Generation')
GO

--Add new Asset Relationships
INSERT INTO AssetRelationshipType (Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Transformer-(Double)Breaker','only Voltage are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}')
GO
INSERT INTO AssetRelationshipType (Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Bus-(Double)Breaker','only Voltage are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}')
GO
INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Transformer'))
GO

INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker'))
GO

-- ------------ --
-- SystemCenter --
-- ------------ --

ALTER TABLE [SystemCenter.Setting] ADD UNIQUE (Name)
GO

CREATE Table [ExternalDatabases] (
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Schedule varchar(50) NULL DEFAULT(NULL),
    ConnectionString Varchar(MAX) NOT NULL,
    DataProviderString VARCHAR(MAX) NULL,
    Encrypt bit NOT NULL DEFAULT(0),
    LastDataUpdate DATETIME2 NULL DEFAULT(NULL),
    Constraint UC_ExternalDatabase UNIQUE(Name)
)

ALTER TABLE extDBTables ADD 
    ExtDBID INT NOT NULL FOREIGN KEY References [ExternalDatabases](ID) DEFAULT(1),
    Constraint UC_ExternalDatabaseTable UNIQUE(TableName, ExtDBID)
GO

ALTER TABLE AdditionalField ADD
	ExternalDBTableID INT NULL FOREIGN KEY REFERENCES [extDBTables](ID),
	IsInfo bit NOT NULL DEFAULT(0),
    IsKey bit NOT NULL DEFAULT(0)
GO

CREATE TRIGGER [dbo].[UO_AdditonaFieldValue] 
ON [dbo].[AdditionalFieldValue]
AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE original SET UpdatedOn = SYSDATETIME()
		FROM dbo.[AdditionalFieldValue] as original INNER JOIN deleted ON 
		original.ID = deleted.ID AND 
		(
			original.Value <> deleted.Value OR
			(original.Value IS NULL AND deleted.Value IS NOT NULL) OR
			(original.Value IS NOT NULL AND deleted.Value IS NULL)
		);
END
GO

ALTER TABLE ExternalOpenXDAField ADD ExternalDBTableID INT NOT NULL FOREIGN KEY References [extDBTables](ID) DEFAULT(1)
GO

ALTER TABLE LocationDrawing ADD
	Number VARCHAR(200) NULL,
    Category VARCHAR(max) NULL
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('PQBrowser.Url', 'http://localhost', 'http://localhost')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('FAWG.Enabled', 'False', 'False')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.Url', 'http://localhost:8989', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.APIKey', '', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.APIToken', '', '')
GO

-- --------- --
-- SEBrowser --
-- --------- --

UPDATE [SEBrowser.Widget] SET Name = 'OpenSEE' WHERE Name = 'EventSearchOpenSEE'
GO
UPDATE [SEBrowser.Widget] SET Type = 'OpenSEE' WHERE Name = 'OpenSEE'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FaultSegments' WHERE Name = 'EventSearchFaultSegments'
GO
UPDATE [SEBrowser.Widget] SET Type = 'EventSearchFaultSegments' WHERE Name = 'FaultSegments'
GO

UPDATE [SEBrowser.Widget] SET Name = 'VoltageDisturbances' WHERE Name = 'EventSearchAssetVoltageDisturbances'
GO
UPDATE [SEBrowser.Widget] SET Type = 'VoltageDisturbances' WHERE Name = 'VoltageDisturbances'
GO

UPDATE [SEBrowser.Widget] SET Name = 'ESRIMap' WHERE Name = 'TVAESRIMap'
GO
UPDATE [SEBrowser.Widget] SET Type = 'ESRIMap' WHERE Name = 'ESRIMap'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FaultInfo' WHERE Name = 'TVAFaultInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'FaultInfo' WHERE Name = 'FaultInfo'
GO

UPDATE [SEBrowser.Widget] SET Type = 'LineParameters' WHERE Name = 'LineParameters'
GO

UPDATE [SEBrowser.Widget] SET Name = 'Lightning' WHERE Name = 'TVALightning'
GO
UPDATE [SEBrowser.Widget] SET Type = 'Lightning' WHERE Name = 'Lightning'
GO

UPDATE [SEBrowser.Widget] SET Name = 'StructureInfo' WHERE Name = 'TVAStructureInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'StructureInfo' WHERE Name = 'StructureInfo'
GO

UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryTable' WHERE Name = 'AssetHistoryTable'
GO

UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryStats' WHERE Name = 'AssetHistoryStats'
GO

UPDATE [SEBrowser.Widget] SET Name = 'CorrelatedSags' WHERE Name = 'EventSearchCorrelatedSags'
GO
UPDATE [SEBrowser.Widget] SET Type = 'CorrelatedSags' WHERE Name = 'CorrelatedSags'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVASIDA' WHERE Name = 'TVASIDA'
GO

UPDATE [SEBrowser.Widget] SET Name = 'SOE' WHERE Name = 'TVASOE'
GO
UPDATE [SEBrowser.Widget] SET Type = 'SOE' WHERE Name = 'SOE'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVASLC' WHERE Name = 'TVASLC'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVAPQWeb' WHERE Name = 'TVAPQWeb'
GO

UPDATE [SEBrowser.Widget] SET Name = 'InterruptionReport' WHERE Name = 'HECCOIR'
GO
UPDATE [SEBrowser.Widget] SET Type = 'InterruptionReport' WHERE Name = 'InterruptionReport'
GO

UPDATE [SEBrowser.Widget] SET Type = 'pqi' WHERE Name = 'pqi'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FileInfo' WHERE Name = 'EventSearchFileInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'FileInfo' WHERE Name = 'FileInfo'
GO

UPDATE [SEBrowser.Widget] SET Name = 'Notes' WHERE Name = 'EventSearchNoteWindow'
GO
UPDATE [SEBrowser.Widget] SET Type = 'Notes' WHERE Name = 'Notes'
GO

UPDATE [SEBrowser.Widget] SET Name = 'RelayPerformance' WHERE Name = 'EventSearchRelayPerformance'
GO
UPDATE [SEBrowser.Widget] SET Type = 'RelayPerformance' WHERE Name = 'RelayPerformance'
GO

UPDATE [SEBrowser.Widget] SET Name = 'BreakerPerformance' WHERE Name = 'EventSearchBreakerPerformance'
GO
UPDATE [SEBrowser.Widget] SET Type = 'BreakerPerformance' WHERE Name = 'BreakerPerformance'
GO

UPDATE [SEBrowser.Widget] SET Name = 'CapBankAnalyticOverview' WHERE Name = 'EventSearchCapBankAnalyticOverview'
GO
UPDATE [SEBrowser.Widget] SET Type = 'CapBankAnalyticOverview' WHERE Name = 'CapBankAnalyticOverview'
GO